package com.yihu.wlyy.service.app.statistics;

import com.google.common.collect.Collections2;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yihu.wlyy.entity.address.City;
import com.yihu.wlyy.entity.address.Hospital;
import com.yihu.wlyy.entity.address.Town;
import com.yihu.wlyy.entity.doctor.Doctor;
import com.yihu.wlyy.repository.*;
import com.yihu.wlyy.service.BaseService;
import com.yihu.wlyy.util.DateUtil;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import redis.clients.jedis.JedisPoolConfig;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by lyr on 2016/08/16.
 */
@Service
public class StatisticsService extends BaseService {
    @Autowired
    CityDao cityDao;
    @Autowired
    TownDao townDao;
    @Autowired
    DoctorDao doctorDao;
    @Autowired
    HospitalDao hospitalDao;


    /******************************************数据库统计指标***************************************/
    // 签约人数
    public static String SIGN = "1";
    // 解约人数
    public static String SURRENDER = "2";
    // 咨询数
    public static String CONSULT = "3";
    // 随访数
    public static String FOLLOWUP = "4";
    // 指导数
    public static String GUIDANCE = "5";
    // 咨询数
    public static String SEX = "6";
    // 随访数
    public static String GROUP = "7";
    // 指导数
    public static String AGE = "8";

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    PeopleNumDao peopleNumDao;
    @Autowired
    private StringRedisTemplate redisTemplate;

    /**
     * 查询截止某个日期累计量
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public long getTotalAmount(String endDate,String area,int level,String index){
        int todayAmount = 0;

        // 查询语句
        String sql = " select " +
                     "     sum(result) amount" +
                     " from  " +
                     "     wlyy_quota_result " +
                     " where " +
                     "     quato_code = '" + index + "' " +
                     "   and level1_type = ? and del = '1'" +
                     "   and quota_date <= ? ";

        if(level == 4){
            // 市级别
            sql += " and city = ? ";
        }else if(level == 3){
            // 区、城镇级别
            sql += " and town = ? ";
        }else if(level == 2){
            // 机构级别
            sql += " and org_code = ? ";
        }else if(level == 1){
            sql += " and qkdoctor_code = ?";
        }

        Map<String,Object> result = jdbcTemplate.queryForMap(sql,new Object[]{level,endDate,area});

        // 截止日期包含当天，则从redis查询当天统计数据
        if(endDate.compareTo(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) >= 0){
            String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + area);
            if(!StringUtils.isEmpty(val)) {
                JSONObject valJson = new JSONObject(val);
                if (valJson.has("num") && valJson.getInt("num") > 0) {
                    todayAmount = valJson.getInt("num");
                }
            }
        }

        if(result != null){
            return (result.get("amount") != null ? Math.round((double)result.get("amount")) : 0) + todayAmount;
        }else{
            return 0 + todayAmount;
        }
    }

    /**
     * 查询截止某个日期累计签约率
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public String getSignRate(String endDate,String area,int level) throws Exception {
        long signAmount = getTotalAmount(endDate,area,level,SIGN);
        long serviceNum = peopleNumDao.findPeopleNumByCode(area);
        DecimalFormat df = new DecimalFormat("0.00");

        if(serviceNum > 0){
            return df.format((signAmount*1.00)/serviceNum*100);
        }else{
            throw new Exception("服务人口为0");
        }
    }

    /**
     * 查询截止某个日期累计建档率
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public String getFilingRate(String endDate,String area,int level){

        return "";
    }

    /**
     * 获取某个指标在某个期间的增长量
     *
     * @param startDate 起始时间
     * @param endDate 结束时间
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public long getIntervalIncrement(String startDate,String endDate,String area,int level,String index){
        int todayAmount = 0;

        // 查询语句
        String sql = " select " +
                "     sum(result) amount" +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = ? and del = '1'" +
                "   and quota_date >= ? " +
                "   and quota_date <= ? ";

        if(level == 4){
            // 市级别
            sql += " and city = ? ";
        }else if(level == 3){
            // 区、城镇级别
            sql += " and town = ? ";
        }else if(level == 2){
            // 机构级别
            sql += " and org_code = ? ";
        }else if(level == 1){
            sql += " and qkdoctor_code = ?";
        }

        Map<String,Object> result = jdbcTemplate.queryForMap(sql,new Object[]{level,startDate,endDate,area});

        // 截止日期包含当天，则从redis查询当天统计数据
        if(endDate.compareTo(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) >= 0){
            String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + area);
            if(!StringUtils.isEmpty(val)) {
                JSONObject valJson = new JSONObject(val);
                if (valJson.has("num") && valJson.getInt("num") > 0) {
                    todayAmount = valJson.getInt("num");
                }
            }
        }

        if(result != null){
            return (result.get("amount") != null ? Math.round((double)result.get("amount")) : 0) + todayAmount;
        }else{
            return 0 + todayAmount;
        }
    }

    /**
     * 查询截止某个日期某个区域后机构各下级累计签约情况
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public JSONArray getLowLevelSignDetail(String endDate, String area, int level){
        String areaField = "";
        String lowLevelField = "";
        String lowLevelName = "";

        if(level == 4){
            // 市级别
            areaField = "city";
            lowLevelField = "town";
            lowLevelName = "town_name";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
            lowLevelField = "org_code";
            lowLevelName = "org_name";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
            lowLevelField = "qkdoctor_code";
            lowLevelName = "qkdoctor_name";
        }else if(level == 1){

        }
        // 查询语句
        String sql = " select " +
                "     ifnull(a." + lowLevelField + ",'') code " +
                "     ,ifnull(a." + lowLevelName + ",'') 'name' " +
                "     ,sum(a.result) amount ";
//        if(level > 3) {
//            sql += "     ,sum(a.result)*1.00/b.num*100 rate";
//        }
        sql += " from  " +
                "     wlyy_quota_result a";
//        if(level > 3) {
//            sql += "  inner join " +
//                    "     wlyy_people_num b" +
//                    "  on a." + lowLevelField + " = b.code ";
//        }
        sql += " where " +
                "     a.quato_code = '" + SIGN + "' " +
                "   and a.level1_type = ? and a.del = '1'" +
                "   and a.quota_date <= ? " +
                "   and a." + areaField + " = ? " +
                " group by a." + lowLevelField + ",a." + lowLevelName;

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql,new Object[]{level - 1,endDate,area});

        if(resultList != null){
            DecimalFormat df = new DecimalFormat("0.00");
            for(Map<String,Object> map : resultList){
                map.put("amount",map.get("amount") != null ? Math.round((double)map.get("amount")) : 0);
//                if(level > 3){
//                    map.put("rate",df.format(map.get("rate") != null ? map.get("rate") : 0));
//                }
            }

            return new JSONArray(resultList);
        }else {
            return new JSONArray();
        }
    }

    /**
     * 查询截止某个日期某个区域后机构各下级累计建档情况
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public JSONArray getLowLevelFilingDetail(String endDate, String area, int level){
        String areaField = "";
        String lowLevelField = "";
        String lowLevelName = "";

        if(level == 4){
            // 市级别
            areaField = "city";
            lowLevelField = "town";
            lowLevelName = "town_name";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
            lowLevelField = "org_code";
            lowLevelName = "org_name";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
            lowLevelField = "qkdoctor_code";
            lowLevelName = "qkdoctor_name";
        }else if(level == 1){

        }
        // 查询语句
        String sql = " select " +
                "     ifnull(" + lowLevelField + ",'') code " +
                "     ,ifnull(" + lowLevelName + ",'') 'name' " +
                "     ,sum(result) amount" +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '' " +
                "   and level1_type = '1' and del = '1'" +
                "   and quota_date <= ? " +
                "   and " + areaField + " = ? " +
                " group by " + lowLevelField + "," + lowLevelName;

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql,new Object[]{endDate,area});

        if(resultList != null){
            for(Map<String,Object> map : resultList){
                map.put("amount",map.get("amount") != null ? Math.round((double)map.get("amount")) : 0);
            }

            return new JSONArray(resultList);
        }else {
            return new JSONArray();
        }
    }

    /**
     * 查询截止某个日期某个区域后机构各下级指标累计情况
     *
     * @param endDate 截止日期
     * @param area 区域或机构代码
     * @param level 级别
     * @return
     */
    public JSONArray getLowLevelTotalDetail(String endDate, String area, int level,String index,int sort){
        String areaField = "";
        String lowLevelField = "";
        String lowLevelName = "";

        if(level == 4){
            // 市级别
            areaField = "city";
            lowLevelField = "town";
            lowLevelName = "town_name";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
            lowLevelField = "org_code";
            lowLevelName = "org_name";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
            lowLevelField = "qkdoctor_code";
            lowLevelName = "qkdoctor_name";
        }else if(level == 1){

        }
        // 查询语句
        String sql = " select " +
                "     ifnull(" + lowLevelField + ",'') code " +
                "     ,ifnull(" + lowLevelName + ",'') 'name' " +
                "     ,ifnull(sum(result),0) amount" +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = ? and del = '1'" +
                "   and quota_date <= ? " +
                "   and " + areaField + " = ? " +
                " group by " + lowLevelField + "," + lowLevelName;

        if(sort == 1){
            sql += " order by amount desc";
        }else{
            sql += " order by amount asc ";
        }

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql,new Object[]{level - 1,endDate,area});
        // 结果为空时,自建结果集
        if(resultList == null || resultList.size() < 1){
            resultList = new ArrayList<>();
            if(level  == 4){
                List<Town> towns = townDao.findByCityCode(area);
                if(towns != null){
                    for(Town town : towns){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",town.getCode());
                        obj.put("name",town.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                    }
                }
            }else if(level == 3){
                List<Hospital> hospitals = hospitalDao.findByTownCode(area);
                if(hospitals != null){
                    for(Hospital hos : hospitals){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",hos.getCode());
                        obj.put("name",hos.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                    }
                }
            }else if(level == 2){
                List<Doctor> doctors = doctorDao.findDoctorByLevelAndHospital(area,2);
                if(doctors != null){
                    for(Doctor doc : doctors){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",doc.getCode());
                        obj.put("name",doc.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                    }
                }
            }
        }

        if(resultList != null){
            for(Map<String,Object> map : resultList){
                map.put("amount",map.get("amount") != null ? Math.round((double)map.get("amount")) : 0);
                // 截止日期包含当天，则从redis查询当天统计数据
                if(endDate.compareTo(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) >= 0){
                    String code = map.get("code") != null ? String.valueOf(map.get("code")) : "";
                    String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + (level - 1) + ":" + code);
                    if(!StringUtils.isEmpty(val)) {
                        JSONObject valJson = new JSONObject(val);
                        if (valJson.has("num") && valJson.getInt("num") > 0) {
                            map.put("amount",(long)map.get("amount") + valJson.getInt("num"));
                        }
                    }
                }
            }

            Collections.sort(resultList, new Comparator<Map<String, Object>>() {
                public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                    long map1value = (long) o1.get("amount");
                    long map2value = (long) o2.get("amount");

                    if(map1value - map2value > 0){
                        return sort == 1 ? -1 : 1;
                    }else if(map1value - map2value < 0){
                        return sort == 1 ? 1 : -1;
                    }else{
                        return 0;
                    }
                }
            });

            return new JSONArray(resultList);
        }else {
            return new JSONArray();
        }
    }

    /**
     * 查询某个日期范围内某个区域或机构各下级指标增量情况
     *
     * @param startDate 起始日期
     * @param endDate 结束日期
     * @param area 区域或机构代码
     * @param level 级别
     * @param index 指标
     * @return
     */
    public JSONArray getLowLevelIncrementDetail(String startDate,String endDate,String area,int level,String index,int sort){
        String areaField = "";
        String lowLevelField = "";
        String lowLevelName = "";

        if(level == 4){
            // 市级别
            areaField = "city";
            lowLevelField = "town";
            lowLevelName = "town_name";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
            lowLevelField = "org_code";
            lowLevelName = "org_name";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
            lowLevelField = "qkdoctor_code";
            lowLevelName = "qkdoctor_name";
        }else if(level == 1){

        }
        // 查询语句
        String sql = " select " +
                "     ifnull(" + lowLevelField + ",'') code " +
                "     ,ifnull(" + lowLevelName + ",'') 'name' " +
                "     ,ifnull(sum(result),0) amount" +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = ? and del = '1'" +
                "   and quota_date >= ? " +
                "   and quota_date <= ? " +
                "   and " + areaField + " = ? " +
                " group by " + lowLevelField + "," + lowLevelName;

        if(sort == 1){
            sql += " order by amount desc";
        }else{
            sql += " order by amount asc ";
        }

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql,new Object[]{level - 1,startDate,endDate,area});

        // 结果为空时,自建结果集
        if(resultList == null || resultList.size() < 1){
            resultList = new ArrayList<>();
            if(level  == 4){
                List<Town> towns = townDao.findByCityCode(area);
                if(towns != null){
                    for(Town town : towns){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",town.getCode());
                        obj.put("name",town.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                        resultList.add(obj);
                    }
                }
            }else if(level == 3){
                List<Hospital> hospitals = hospitalDao.findByTownCode(area);
                if(hospitals != null){
                    for(Hospital hos : hospitals){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",hos.getCode());
                        obj.put("name",hos.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                        resultList.add(obj);
                    }
                }
            }else if(level == 2){
                List<Doctor> doctors = doctorDao.findDoctorByLevelAndHospital(area,2);
                if(doctors != null){
                    for(Doctor doc : doctors){
                        Map<String,Object> obj =  new HashMap<>();
                        obj.put("code",doc.getCode());
                        obj.put("name",doc.getName());
                        obj.put("amount",Double.valueOf("0.0"));
                        resultList.add(obj);
                    }
                }
            }
        }

        if(resultList != null){
            for(Map<String,Object> map : resultList){
                map.put("amount",map.get("amount") != null ? Math.round((double)map.get("amount")) : 0);
                // 截止日期包含当天，则从redis查询当天统计数据
                if(endDate.compareTo(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) >= 0){
                    String code = map.get("code") != null ? String.valueOf(map.get("code")) : "";
                    String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + (level - 1) + ":" + code);
                    if(!StringUtils.isEmpty(val)) {
                        JSONObject valJson = new JSONObject(val);
                        if (valJson.has("num") && valJson.getInt("num") > 0) {
                            map.put("amount",(long)map.get("amount") + valJson.getInt("num"));
                        }
                    }
                }
            }

            Collections.sort(resultList, new Comparator<Map<String, Object>>() {
                public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                    long map1value = (long) o1.get("amount");
                    long map2value = (long) o2.get("amount");

                    if(map1value - map2value > 0){
                        return sort == 1 ? -1 : 1;
                    }else if(map1value - map2value < 0){
                        return sort == 1 ? 1 : -1;
                    }else{
                        return 0;
                    }
                }
            });

            return new JSONArray(resultList);
        }else {
            return new JSONArray();
        }
    }

    /**
     * 获取时间范围内按间隔统计指标增量
     *
     * @param startDate 起始时间
     * @param endDate 结束时间
     * @param interval 时间间隔
     * @param area 区域或机构代码
     * @param level 级别
     * @param index 指标
     * @return
     */
    public JSONArray getDateIncrementDetail(String startDate,String endDate,int interval,String area,int level,String index) throws Exception {
        if(interval == 1) {
            return dateStatistics(startDate,endDate,area,level,index);
        }
        else if(interval == 2){
            return weekStatistics(startDate,endDate,area,level,index);
        }
        else if(interval == 3) {
            return monthStatistics(startDate,endDate,area,level,index);
        }

        return new JSONArray();
    }

    /**
     * 按日统计
     *
     * @param startDate
     * @param endDate
     * @param area
     * @param level
     * @param index
     * @return
     */
    private JSONArray dateStatistics(String startDate,String endDate,String area,int level,String index){
        String today = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String areaField = "";
        String sql ="";

        if(level == 4){
            // 市级别
            areaField = "city";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
        }else if(level == 1){
            // 机构级别
            areaField = "qkdoctor_code";
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        // 起始日期
        Calendar start = Calendar.getInstance();
        start.setTime(DateUtil.strToDate(startDate,DateUtil.YYYY_MM_DD));
        //结束日期
        Calendar end = Calendar.getInstance();
        end.setTime(DateUtil.strToDate(endDate,DateUtil.YYYY_MM_DD));
        // 日期集合
        List<Calendar> days = new ArrayList<>();
        days.add(start);

        boolean flag = true;

        if(startDate.compareTo(endDate) == 0){
            flag = false;
        }
        // 计算统计日期
        while(flag){
            Calendar next = Calendar.getInstance();
            next.setTime(days.get(days.size() - 1).getTime());
            next.add(Calendar.DATE,1);
            if(df.format(next.getTime()).compareTo(endDate) < 0){
                days.add(next);
            }else{
                days.add(end);
                flag = false;
            }
        }

        Map<String,JSONObject> countResult = new HashMap<>();

        // 统计预计构建
        for(int i = 0;i < days.size() ; i++) {
            String startStr = "";
            long amount = 0;

            startStr = df.format(days.get(i).getTime());

            // 当前范围包含当天，则需添加当天的统计数据
            if(startStr.compareTo(today) == 0) {
                String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + area);
                if(!StringUtils.isEmpty(val)) {
                    JSONObject valJson = new JSONObject(val);
                    if (valJson.has("num") && valJson.getInt("num") > 0) {
                        amount = (long)valJson.getInt("num");
                    }
                }
            }

            JSONObject range = new JSONObject();
            range.put("range",startStr);
            range.put("amount",amount);
            countResult.put(startStr,range);
        }

        sql =  " select " +
                "     ifnull(quota_date,'') as 'range' " +
                "     ,ifnull(sum(result),0) amount " +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = '" + level + "' and del = '1' " +
                "   and quota_date >= '" + startDate + "' " +
                "   and quota_date <= '" + endDate + "' " +
                "   and " + areaField + " = '" + area + "' " +
                "   group by quota_date ";

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql);

        if(resultList != null){
            for(Map<String,Object> map : resultList){
                if(countResult.containsKey(map.get("range").toString())){
                    JSONObject range = (JSONObject)countResult.get(map.get("range").toString());
                    long amount = range.getLong("amount");
                    long resultAmount = map.get("amount") != null ? Math.round((double)map.get("amount")) : 0;
                    range.put("amount",amount + resultAmount);
                }
            }

            List<JSONObject> result = new ArrayList<>(countResult.values());

            result.sort(new Comparator<JSONObject>() {
                @Override
                public int compare(JSONObject o1, JSONObject o2) {
                    if(o1.getString("range").compareTo(o2.getString("range")) > 0){
                        return 1;
                    }else if(o1.getString("range").compareTo(o2.getString("range")) < 0){
                        return -1;
                    }else{
                        return 0;
                    }
                }
            });

            return new JSONArray(result);
        }else {
            return new JSONArray();
        }
    }

    /**
     * 按周统计
     *
     * @param startDate
     * @param endDate
     * @param area
     * @param level
     * @param index
     * @return
     */
    private JSONArray weekStatistics(String startDate,String endDate,String area,int level,String index) throws Exception {
        String today = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String areaField = "";
        String sql ="";

        if(level == 4){
            // 市级别
            areaField = "city";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
        }else if(level == 1){
            // 机构级别
            areaField = "qkdoctor_code";
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        // 起始日期
        Calendar start = Calendar.getInstance();
        start.setTime(DateUtil.strToDate(startDate,DateUtil.YYYY_MM_DD));
        // 第一个统计周期结束日期
        String firstEnd = "";
        // 结束日期
        Calendar end = Calendar.getInstance();
        end.setTime(DateUtil.strToDate(endDate,DateUtil.YYYY_MM_DD));
        // 起始日期为周几
        int week = start.get(Calendar.DAY_OF_WEEK);
        int incre = 7 - week + 1;

        // 日期集合
        List<Calendar> days = new ArrayList<>();
        days.add(start);
        boolean flag = true;
        int i = 0;

        if(startDate.compareTo(endDate) == 0){
            flag = false;
            days.add(end);
            firstEnd = df.format(end.getTime());
        }

        // 计算统计日期
        while(flag){
            Calendar next = Calendar.getInstance();
            next.setTime(days.get(days.size() - 1).getTime());

            if(i == 0) {
                if(incre != 7) {
                    next.add(Calendar.DATE, incre);
                }
            }else{
                next.add(Calendar.DATE, 7);
            }
            if(df.format(next.getTime()).compareTo(df.format(end.getTime())) < 0){
                days.add(next);
                if(i == 0){
                    firstEnd = df.format(next.getTime());
                }
            }else{
                days.add(end);
                flag = false;
                if(i == 0){
                    firstEnd = df.format(end.getTime());
                }
            }

            i++;
        }

        // 结果集
        Map<String,JSONObject> countResult = new HashMap<>();

        // 算出每个查询周期
        for(int j = 0; j < days.size() - 1;j++){
            String startStr = "";
            String endStr = df.format(days.get(j + 1).getTime());
            long amount = 0;

            // 起始、截止日期
            if(j == 0){
                startStr = df.format(days.get(j).getTime());
            }else{
                Calendar cal = Calendar.getInstance();
                cal.setTime(days.get(j).getTime());
                cal.add(Calendar.DATE,1);
                startStr = df.format(cal.getTime());
            }

            // 当前范围包含当天，则需添加当天的统计数据
            if(startStr.compareTo(today) <= 0 && endStr.compareTo(today) >= 0) {
                String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + area);
                if(!StringUtils.isEmpty(val)) {
                    JSONObject valJson = new JSONObject(val);
                    if (valJson.has("num") && valJson.getInt("num") > 0) {
                        amount = valJson.getInt("num");
                    }
                }
            }

            JSONObject range =  new JSONObject();

            range.put("range",startStr);
            range.put("amount",amount);

            countResult.put(startStr + ":" + endStr,range);
        }

        // 查询时间范围内所有记录
        sql =  " select " +
                "     ifnull(quota_date,'') as 'range' " +
                "     ,ifnull(sum(result),0) amount " +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = '" + level + "' and del = '1' " +
                "   and quota_date >= '" + startDate + "' " +
                "   and quota_date <= '" + endDate + "' " +
                "   and " + areaField + " = '" + area + "' " +
                "   group by quota_date ";

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql);

        if(resultList != null){
            // 计算结果
            for(Map<String,Object> map : resultList){
                long resultAmount = map.get("amount") != null ? Math.round((double)map.get("amount")) : 0;
                String range = map.get("range").toString();

                if(org.apache.commons.lang3.StringUtils.isNotEmpty(range)){
                    // 起始日期
                    String startStr = "";
                    // 结束日期
                    String endStr = "";

                    if(range.compareTo(startDate) >= 0 && range.compareTo(firstEnd) <= 0){
                        startStr =  startDate;
                        endStr = firstEnd;
                    }else{
                        Calendar cal = Calendar.getInstance();
                        cal.setTime(df.parse(range));
                        int calWeek =  cal.get(Calendar.DAY_OF_WEEK) - 2;

                        if(calWeek == -1){
                            cal.add(Calendar.DATE, -6);
                        }else{
                            cal.add(Calendar.DATE, -calWeek);
                        }

                        startStr = df.format(cal.getTime());
                        cal.add(Calendar.DATE,6);
                        endStr = df.format(cal.getTime());

                        if(endStr.compareTo(endDate) > 0){
                            endStr = endDate;
                        }
                    }

                    JSONObject json = countResult.get(startStr + ":" + endStr);
                    json.put("amount",json.getLong("amount") + resultAmount);
                }
            }

            List<JSONObject> result = new ArrayList<>(countResult.values());

            // 排序
            result.sort(new Comparator<JSONObject>() {
                @Override
                public int compare(JSONObject o1, JSONObject o2) {
                    if(o1.getString("range").compareTo(o2.getString("range")) > 0){
                        return 1;
                    }else if(o1.getString("range").compareTo(o2.getString("range")) < 0){
                        return -1;
                    }else{
                        return 0;
                    }
                }
            });

            return new JSONArray(result);
        }else{
            return new JSONArray();
        }
    }

    /**
     * 按月统计
     *
     * @param startDate
     * @param endDate
     * @param area
     * @param level
     * @param index
     * @return
     * @throws Exception
     */
    private JSONArray monthStatistics(String startDate,String endDate,String area,int level,String index) throws Exception{
        String today = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String areaField = "";
        String sql ="";

        if(level == 4){
            // 市级别
            areaField = "city";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
        }else if(level == 1){
            // 机构级别
            areaField = "qkdoctor_code";
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        // 起始日期
        Calendar start = Calendar.getInstance();
        start.setTime(DateUtil.strToDate(startDate,DateUtil.YYYY_MM_DD));
        // 结束日期
        Calendar end = Calendar.getInstance();
        end.setTime(DateUtil.strToDate(endDate,DateUtil.YYYY_MM_DD));
        // 第一个结束日期
        String firstEnd = "";

        // 日期集合
        List<Calendar> days = new ArrayList<>();
        days.add(start);

        boolean flag = true;
        int k = 0;

        if(startDate.compareTo(endDate) == 0){
            flag = false;
            days.add(end);
            firstEnd = df.format(end.getTime());
        }

        // 统计日期计算
        while(flag){
            Calendar next = Calendar.getInstance();

            next.setTime(days.get(days.size() - 1).getTime());
            if(k == 0){
                next.add(Calendar.MONTH, 1);
            }else{
                next.add(Calendar.MONTH, 2);
            }
            next.set(Calendar.DAY_OF_MONTH, 1);
            next.add(Calendar.DAY_OF_MONTH, -1);

            if(df.format(next.getTime()).compareTo(df.format(end.getTime())) < 0){
                days.add(next);
                if(k == 0){
                    firstEnd = df.format(next.getTime());
                }
            }else{
                days.add(end);
                flag = false;
                if(k == 0){
                    firstEnd = df.format(end.getTime());
                }
            }
            k++;
        }

        // 统计结果
        Map<String,JSONObject> countResult = new HashMap<>();

        for(int i = 0; i < days.size() - 1; i++) {
            String startStr = "";
            String endStr = df.format(days.get(i + 1).getTime());
            int amount = 0;

            // 起始时间计算
            if(i == 0){
                startStr = df.format(days.get(i).getTime());
            }else{
                Calendar cal = Calendar.getInstance();
                cal.setTime(days.get(i).getTime());
                cal.add(Calendar.DATE,1);
                startStr = df.format(cal.getTime());
            }

            // 当天数据计算
            if(startStr.compareTo(today) <= 0 && endStr.compareTo(today) >= 0) {
                String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + area);
                if(!StringUtils.isEmpty(val)) {
                    JSONObject valJson = new JSONObject(val);
                    if (valJson.has("num") && valJson.getInt("num") > 0) {
                        amount = valJson.getInt("num");
                    }
                }
            }

            JSONObject range =  new JSONObject();

            range.put("range",startStr);
            range.put("amount",amount);

            countResult.put(startStr + ":" + endStr,range);
        }

        // 查询时间范围内所有记录
        sql =  " select " +
                "     ifnull(quota_date,'') as 'range' " +
                "     ,ifnull(sum(result),0) amount " +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = '" + level + "' and del = '1' " +
                "   and quota_date >= '" + startDate + "' " +
                "   and quota_date <= '" + endDate + "' " +
                "   and " + areaField + " = '" + area + "' " +
                "   group by quota_date ";

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sql);

        if(resultList != null){
            // 计算结果
            for(Map<String,Object> map : resultList){
                long resultAmount = map.get("amount") != null ? Math.round((double)map.get("amount")) : 0;
                String range = map.get("range").toString();

                if(org.apache.commons.lang3.StringUtils.isNotEmpty(range)){
                    // 起始日期
                    String startStr = "";
                    // 结束日期
                    String endStr = "";

                    if(range.compareTo(startDate) >= 0 && range.compareTo(firstEnd) <= 0){
                        startStr =  startDate;
                        endStr = firstEnd;
                    }else{
                        Calendar cal = Calendar.getInstance();
                        cal.setTime(df.parse(range));
                        cal.set(Calendar.DATE, 1);

                        startStr = df.format(cal.getTime());
                        cal.add(Calendar.MONTH,1);
                        cal.set(Calendar.DATE,1);
                        cal.add(Calendar.DATE,-1);
                        endStr = df.format(cal.getTime());

                        if(endStr.compareTo(endDate) > 0){
                            endStr = endDate;
                        }
                    }

                    JSONObject json = countResult.get(startStr + ":" + endStr);
                    json.put("amount",json.getLong("amount") + resultAmount);
                }
            }

            List<JSONObject> result = new ArrayList<>(countResult.values());

            // 排序
            result.sort(new Comparator<JSONObject>() {
                @Override
                public int compare(JSONObject o1, JSONObject o2) {
                    if(o1.getString("range").compareTo(o2.getString("range")) > 0){
                        return 1;
                    }else if(o1.getString("range").compareTo(o2.getString("range")) < 0){
                        return -1;
                    }else{
                        return 0;
                    }
                }
            });

            return new JSONArray(result);
        }else{
            return new JSONArray();
        }
    }


    /**
     * 获取二级维度下指标时间范围内增量
     *
     * @param startDate 起始时间
     * @param endDate 结束时间
     * @param area 区域或机构代码
     * @param level 级别
     * @param index 指标
     * @return
     */
    public JSONArray getLevelTwoIndexIncrement(String startDate,String endDate,String area,int level,String index){
        String areaField = "";

        if(level == 4){
            // 市级别
            areaField = "city";
        }else if(level == 3){
            // 区、城镇级别
            areaField = "town";
        }else if(level == 2){
            // 机构级别
            areaField = "org_code";
        }else if(level == 1){
            // 团队
            areaField = "qkdoctor_code";
        }
        // 查询语句
        String sql = " select " +
                "     ifnull(level2_type,'') code " +
                "     ,ifnull(level2_type_name,'') 'name' " +
                "     ,ifnull(sum(result),0) amount" +
                " from  " +
                "     wlyy_quota_result " +
                " where " +
                "     quato_code = '" + index + "' " +
                "   and level1_type = ? and del = '1'";


        if(!org.apache.commons.lang3.StringUtils.isEmpty(startDate)){
            sql += "   and quota_date >= ? ";
        }

        sql +=  "   and quota_date <= ? " +
                "   and " + areaField + " = ? " +
                " group by level2_type,level2_type_name";

        List<Map<String,Object>> resultList = null;

        if(org.apache.commons.lang3.StringUtils.isEmpty(startDate)){
            resultList = jdbcTemplate.queryForList(sql,new Object[]{level,endDate,area});
        }else {
            resultList = jdbcTemplate.queryForList(sql, new Object[]{level, startDate, endDate, area});
        }

        if(resultList == null || resultList.size() < 1) {
            resultList = new ArrayList<>();
            if(index.equals(SEX)){
                Map<String,Object> women = new HashMap<>();
                women.put("code","1");
                women.put("name","女");
                women.put("amount",Double.valueOf("0.0"));
                resultList.add(women);

                Map<String,Object> man = new HashMap<>();
                man.put("code","2");
                man.put("name","男");
                man.put("amount",Double.valueOf("0.0"));
                resultList.add(man);

                Map<String,Object> unknown = new HashMap<>();
                unknown.put("code","3");
                unknown.put("name","未知");
                unknown.put("amount",Double.valueOf("0.0"));
                resultList.add(unknown);
            }else if(index.equals(GROUP)){
                Map<String,Object> normal = new HashMap<>();
                normal.put("code","1");
                normal.put("name","普通人群");
                normal.put("amount",Double.valueOf("0.0"));
                resultList.add(normal);

                Map<String,Object> manbing = new HashMap<>();
                manbing.put("code","2");
                manbing.put("name","慢病人群");
                manbing.put("amount",Double.valueOf("0.0"));
                resultList.add(manbing);

                Map<String,Object> upsixfive = new HashMap<>();
                upsixfive.put("code","3");
                upsixfive.put("name","65岁以上人群");
                upsixfive.put("amount",Double.valueOf("0.0"));
                resultList.add(upsixfive);

                Map<String,Object> gxy = new HashMap<>();
                gxy.put("code","4");
                gxy.put("name","高血压");
                gxy.put("amount",Double.valueOf("0.0"));
                resultList.add(gxy);

                Map<String,Object> tnb = new HashMap<>();
                tnb.put("code","5");
                tnb.put("name","糖尿病");
                tnb.put("amount",Double.valueOf("0.0"));
                resultList.add(tnb);
            }else if(index.equals(AGE)){
                Map<String,Object> map1= new HashMap<>();
                map1.put("code","1");
                map1.put("name","0~6");
                map1.put("amount",Double.valueOf("0.0"));
                resultList.add(map1);

                Map<String,Object> map2 = new HashMap<>();
                map2.put("code","2");
                map2.put("name","7~18");
                map2.put("amount",Double.valueOf("0.0"));
                resultList.add(map2);

                Map<String,Object> map3 = new HashMap<>();
                map3.put("code","3");
                map3.put("name","18~30");
                map3.put("amount",Double.valueOf("0.0"));
                resultList.add(map3);

                Map<String,Object> map4 = new HashMap<>();
                map4.put("code","4");
                map4.put("name","30~50");
                map4.put("amount",Double.valueOf("0.0"));
                resultList.add(map4);

                Map<String,Object> map5 = new HashMap<>();
                map5.put("code","5");
                map5.put("name","50~65");
                map5.put("amount",Double.valueOf("0.0"));
                resultList.add(map5);

                Map<String,Object> map6 = new HashMap<>();
                map6.put("code","6");
                map6.put("name","50~65");
                map6.put("amount",Double.valueOf("0.0"));
                resultList.add(map6);
            }
        }

        if(resultList != null){
            long total = 0;

            for(Map<String,Object> map : resultList){
                map.put("amount",map.get("amount") == null ? 0 : Math.round((double)map.get("amount")));

                // 当天数据统计
                if(endDate.compareTo(new SimpleDateFormat("yyyy-MM-dd").format(new Date())) >= 0){
                    String code = map.get("code") != null ? String.valueOf(map.get("code")) : "";
                    String val =  redisTemplate.opsForValue().get("quota:" + index + ":" + level + ":" + code +":" + area);
                    if(!StringUtils.isEmpty(val)) {
                        JSONObject valJson = new JSONObject(val);
                        if (valJson.has("num") && valJson.getInt("num") > 0) {
                            map.put("amount",(long)map.get("amount") + valJson.getInt("num"));
                        }
                    }
                }

                if(index.equals(GROUP)){
                    // 分组指标总数算法
                    String code = map.get("code") != null ? String.valueOf(map.get("code")) : "";
                    if(!code.equals("2")){
                        total += (long)map.get("amount");
                    }
                }else{
                    total += (long)map.get("amount");
                }
            }
            if(!index.equals(AGE)) {
                DecimalFormat df = new DecimalFormat("0.00");
                double rateTotal = 0.00;
                for (Map<String, Object> map : resultList) {
                    double rateG = (total > 0 ? ((long) map.get("amount")) * 1.00 / total * 100 : 0);
                    map.put("rate", df.format(rateG));
                    rateTotal += rateG;
                }

                if(1 - rateTotal > 0) {
                    for (Map<String, Object> map : resultList) {
                        if ((long) map.get("amount") > 0) {
                            map.put("rate",df.format(Double.valueOf((String)map.get("rate")) +(1 - rateTotal)));
                            break;
                        }
                    }
                }
            }
            if(index.equals(SEX) && resultList.size() > 0){
                int i = 0;
                boolean flag = false;
                for (;i < resultList.size(); i++) {
                    if(resultList.get(i).get("code") != null && String.valueOf(resultList.get(i).get("code")).equals("3")
                            && String.valueOf(resultList.get(i).get("amount")).equals("0")) {
                        flag = true;
                        break;
                    }
                }
                if(flag){
                    resultList.remove(i);
                }
            }
            return new JSONArray(resultList);
        }else {
            return new JSONArray();
        }
    }

}
